package com.ht.api.db;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import javax.sql.DataSource;

import com.ht.api.db.bean.Column;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;

/**
 * sql创建者，存储诸多查询条件，可翻译成sql并执行
 * @author asq
 * @createTime 2024年12月2日 21:17:39
 */
@Getter
@Setter
@Accessors(chain = true)
@Slf4j
public class SqlParser extends JSONObject{
	/** sql的组成部分：select */
	private static final String SELECT = "select";
	/** sql查询字段的过滤部分 */
	private static final String SELECT_FILTER = "select_filter";
	/** sql的组成部分：where */
	private static final String WHERE = "where";
	/** sql的组成部分：order */
	public static final String ORDER = "order";
	/** sql的组成部分：having */
	private static final String HAVING = "having";
	/** sql的组成部分：group */
	private static final String GROUP = "group";
	/** sql的组成部分：limit */
	private static final String LIMIT = "limit";
	
	/** json字段key：field */
	private static final String FIELD = "field";
	/** json字段key：operator */
	private static final String OPERATOR = "operator";
	/** json字段key：value */
	private static final String VALUE = "value";
	/** json字段key：subQuery */
	private static final String SUBQUERY = "subquery";
	/** json字段key：sort */
	private static final String SORT = "sort";
	/** json字段key：pagenum */
	private static final String PAGENUM = "pagenum";
	/** json字段key：pagesize */
	private static final String PAGESIZE = "pagesize";
	
    /** 数据库表名称 */
    private String table;
    /** sql对应的参数列表 */
    private List<Object> paramList = new ArrayList<>();

    /**
     * 私有化构造函数
     * @param table
     */
    private SqlParser(String table) {
        this.table = table;
        this.set("table", table);
    }
    
    /**
     * 创建一个实例
     * @param table 表名
     * @return
     */
    public static SqlParser create(String table) {
    	return new SqlParser(table);
    }

    /**
     * 覆盖JSONObject的set方法，返回Db，方便链式调用
     */
    public SqlParser set(String key, Object value) {
        super.set(key, value);
        return this;
    }
	
	/**
	 * 创建select字段列表
	 * @param fieldNames 字段列表
	 * @return
	 */
    public SqlParser select(String fieldNames) {
    	set(SELECT, fieldNames);
        return this;
    }
    
    /**
     * 排除指定字段
     * @param fieldNames 字段列表
     * @return
     */
    public SqlParser selectFilter(String fieldNames) {
    	set(SELECT_FILTER, fieldNames);
    	return this;
    }

	/**
	 * 创建一个where条件，使用“=”操作符
	 * @param field 字段名
	 * @param value 值
	 * @return
	 */
    public SqlParser where(String field, Object value) {
        return this.where(field, "=", value);
    }

	/**
	 * 创建一个where条件，子查询
	 * @param field 字段名
	 * @param value 值
	 * @return
	 */
	public SqlParser subwhere(String field, String operator, Object value) {
		JSONObject condition = JSONUtil.createObj();
		condition.set(FIELD, field);
		condition.set(OPERATOR, operator);
		condition.set(VALUE, value);
		condition.set(SUBQUERY, true);
		getComponent(WHERE).add(condition);
		return this;
	}

    /**
     * 创建一个where条件，自定义操作符
     * @param field 字段名
     * @param operator 操作符
     * @param value 值
     * @return
     */
    public SqlParser where(String field, String operator, Object value) {
        JSONObject condition = JSONUtil.createObj();
        condition.set(FIELD, field);
        condition.set(OPERATOR, operator);
        condition.set(VALUE, value);
        getComponent(WHERE).add(condition);
        return this;
    }

    /**
     * 创建一个having条件，自定义操作符
     * @param field 字段名
     * @param operator 操作符
     * @param value 值
     * @return
     */
    public SqlParser having(String field, String operator, Object value) {
        JSONObject condition = JSONUtil.createObj();
        condition.set(FIELD, field);
        condition.set(OPERATOR, operator);
        condition.set(VALUE, value);
        getComponent(HAVING).add(condition);
        return this;
    }

    /**
     * 创建group字段列表
     * @return
     */
    public SqlParser group(String fieldNames) {
    	set(GROUP, fieldNames);
        return this;
    }

    /**
     * 创建一个order by条件
     * @param field 字段名
     * @param sort 升序：asc，降序：desc
     * @return
     */
    public SqlParser order(String field, String sort) {
        JSONObject condition = JSONUtil.createObj();
        condition.set(FIELD, field);
        condition.set(SORT, sort);
        getComponent(ORDER).add(condition);
        return this;
    }

    /**
     * 创建limit分页
     * @param pagenum 第几页（第1页从1开始）
     * @param size 每页条数
     * @return
     */
    public SqlParser limit(int pagenum, int size) {
        JSONObject limit = JSONUtil.createObj();
        limit.set(PAGENUM, pagenum);
        limit.set(PAGESIZE, size);
        set(LIMIT, limit);
        return this;
    }

    /**
     * 创建一个简单的limit分页
     * @param limit 数据条数
     * @return
     */
    public SqlParser limit(int limit) {
    	return limit(1, limit);
    }
    
    /**
     * 获取分页信息的第几页
     * @return
     */
    public Integer getPageNum() {
    	Object pageNum = this.getByPath(LIMIT + "." + PAGENUM);
		return pageNum != null ? (Integer)pageNum : null;
    }
    
    /**
     * 获取分面信息的每页显示条数
     * @return
     */
    public Integer getPageSize() {
    	Object pageSize = this.getByPath(LIMIT + "." + PAGESIZE);
		return pageSize != null ? (Integer)pageSize : null;
    }
    
    /**
     * 获取sql语句
     * @return
     */
    public String getSql() {
    	return getSql(this);
    }
    
    /**
     * 获取原生sql语句（不带参数问号）
     * @return
     */
    public String getOriginalSql() {
    	return getOriginalSql(this);
    }
    
    // =========================================
    // 快速查询方法-----start----------------------
    /**
     * 默认数据源：根据ID查找对象
     * @param id id值
     * @return
     */
    public Entity findById(Object id) {
    	this.where("main_id", id);
		return Db.findOneByTable(this.table, this.getSql(), this.getParamList());
	}
    
    /**
     * 根据ID查找对象
	 * @param ds 指定数据源
     * @param id id值
     * @return
     */
    public Entity findById(DataSource ds, Object id) {
    	this.where("main_id", id);
    	return Db.findOneByTable(ds, this.table, this.getSql(), this.getParamList());
    }
    
    /**
     * 默认数据源：查询一个实体（只取结果集的第一条）
     * @return
     */
    public Entity findOne() {
		return Db.findOneByTable(this.table, this.getSql(), this.getParamList());
	}
    
    /**
     * 查询一个实体（只取结果集的第一条）
	 * @param ds 指定数据源
     * @return
     */
    public Entity findOne(DataSource ds) {
    	return Db.findOneByTable(ds, this.table, this.getSql(), this.getParamList());
    }
    
    /**
     * 默认数据源：查询列表
     * @return
     */
    public DbList query() {
		return Db.queryByTable(this.table, this.getSql(), this.getParamList());
	}
    
    /**
     * 查询列表
	 * @param ds 指定数据源
     * @return
     */
    public DbList query(DataSource ds) {
    	return Db.queryByTable(ds, this.table, this.getSql(), this.getParamList());
    }
    
    /**
     * 默认数据源：查询count
     * @return
     */
    public long count() {
    	return count(null);
    }
    
    /**
     * 查询count
	 * @param ds 指定数据源
     * @return
     */
    public long count(DataSource ds) {
    	// 去掉order by语句
    	this.remove(ORDER);
    	
    	// 去掉limit语句
    	this.remove(LIMIT);

    	// 查询count要根据是否有group语句来判断
    	String sql = null;
    	if (this.containsKey(GROUP)) {
    		sql = String.format("select count(*) from (%s)sub_query", this.getSql());
    	} else {
	    	// 用sum(sys_deleted)代替count(*)
	    	this.select("sum(sys_deleted)");
	    	sql = this.getSql();
    	}
    	
    	if (ds != null) {
    		return Db.count(ds, sql, this.getParamList());
    	} else {
    		return Db.count(sql, this.getParamList());
    	}
    }
    
    /**
     * 默认数据源：查询单个值，通常用于聚合函数
     * @param <E>
     * @return
     */
    public <E> E val() {
    	return Db.val(this.getSql(), this.getParamList());
    }
    
    /**
     * 查询单个值，通常用于聚合函数
	 * @param ds 指定数据源
     * @param <E>
     * @return
     */
    public <E> E val(DataSource ds) {
    	return Db.val(ds, this.getSql(), this.getParamList());
    }
    
    /**
     * 默认数据源：查询单个值，通常用于聚合函数
     * @param <E>
     * @return
     */
    public <E> E val(Class<E> clazz) {
    	return Db.val(this.getSql(), clazz, this.getParamList());
    }
    
    /**
     * 查询单个值，通常用于聚合函数
	 * @param ds 指定数据源
     * @param <E>
     * @return
     */
    public <E> E val(DataSource ds, Class<E> clazz) {
    	return Db.val(ds, this.getSql(), clazz, this.getParamList());
    }
    
    /**
     * 默认数据源：判断数据是否存在
     * @return
     */
    public boolean hasRow() {
    	return Db.hasRow(this.getSql(), this.getParamList());
    }
    
    /**
     * 判断数据是否存在
	 * @param ds 指定数据源
     * @return
     */
    public boolean hasRow(DataSource ds) {
    	return Db.hasRow(ds, this.getSql(), this.getParamList());
    }
    // 快速查询方法-----end----------------------
    // =========================================

    /**
     * 获取一个JSONArray，如果没有，则创建之
     * @param name 对象名
     * @return {@link JSONArray}
     */
	private JSONArray getComponent(String name) {
		JSONArray component = getJSONArray(name);
        if (component == null) {
            component = JSONUtil.createArray();
            set(name, component);
        }
		return component;
	}

    /**
     * 翻译sqlJSON字段为sql
     * @param 
     * @return
     */
    @SuppressWarnings("unchecked")
	private String getSql(SqlParser sqlParser) {
    	// select * from `user` where `a` = ? and `b` = ? group by age,name having count(*)>1 order by age desc limit 1,10
    	List<Object> params = new ArrayList<>(); // 记录所有的参数值
    	StringBuilder sql = new StringBuilder();
    	sql.append("select ");
    	
    	// 翻译查询的select
    	String filterSelectFields = getFilterSelectFields(sqlParser);
		sql.append(filterSelectFields).append(String.format(" from `%s`", sqlParser.getTable()));
    	
    	// 翻译查询的where
    	StringBuilder sqlWhere = new StringBuilder();
    	JSONArray arrWhere = sqlParser.getJSONArray(WHERE);
    	if (arrWhere != null && !arrWhere.isEmpty()) {
    		for (int i = 0; i < arrWhere.size(); i++) {
    			JSONObject where = arrWhere.getJSONObject(i);
    			Object value = where.get(VALUE);
    			String operator = where.getStr(OPERATOR).toLowerCase();
    			SqlParser subSqlCreator = getSubSqlParser(where);
				if (subSqlCreator != null) {
					String subQuery = getSql(subSqlCreator);
    				sqlWhere.append(String.format("%s %s (%s) and ", getWhere(where), operator, subQuery));
    				params.addAll(subSqlCreator.getParamList());
    			} else {
					if (!where.isNull(SUBQUERY) && where.getBool(SUBQUERY)) { // sql子查询
						sqlWhere.append(String.format("%s %s (%s) and ", getWhere(where), operator, value));
					} else {
						if ("in".equals(operator) || "not in".equals(operator)) {
							List<Object> valueArr = (List<Object>)value;
							sqlWhere.append(String.format("%s %s (", getWhere(where), where.getStr(OPERATOR)));
							valueArr.stream().forEach(v -> {
								sqlWhere.append("?,");
								params.add(v);
							});
							sqlWhere.deleteCharAt(sqlWhere.length() - 1).append(String.format(") and "));
						} else {
							sqlWhere.append(String.format("%s %s ? and ", getWhere(where), operator));
							params.add(value);
						}
					}
    			}
    		}
    		sqlWhere.setLength(sqlWhere.length() - 5); // 去掉末尾的and
    	}
    	if (!sqlWhere.isEmpty()) {
        	sql.append(String.format(" where sys_deleted=1 and %s", sqlWhere.toString()));
    	} else {
    		sql.append(" where sys_deleted=1");
    	}
    	
    	// 翻译查询的group
    	String groupFields = sqlParser.getStr(GROUP);
		sql.append(groupFields != null ? String.format(" group by %s", groupFields) : "");
    	
    	// 翻译查询的having
    	StringBuilder sqlHaving = new StringBuilder();
    	JSONArray arrHaving = sqlParser.getJSONArray(HAVING);
    	if (arrHaving != null && !arrHaving.isEmpty()) {
    		for (int i = 0; i < arrHaving.size(); i++) {
    			JSONObject having = arrHaving.getJSONObject(i);
    			sqlHaving.append(String.format("%s %s ? and ", having.getStr(FIELD), having.getStr(OPERATOR)));
    			params.add(having.get(VALUE));
    		}
    		sqlHaving.setLength(sqlHaving.length() - 4); // 去掉末尾的and
    	}
    	if (!sqlHaving.isEmpty()) {
        	sql.append(String.format(" having %s", sqlHaving.toString()));
    	}
    	
    	// 翻译查询的order
    	StringBuilder sqlOrder = new StringBuilder();
    	JSONArray arrOrder = sqlParser.getJSONArray(ORDER);
    	if (arrOrder != null && !arrOrder.isEmpty()) {
    		for (int i = 0; i < arrOrder.size(); i++) {
    			JSONObject order = arrOrder.getJSONObject(i);
    			sqlOrder.append(String.format("`%s` %s,", order.getStr(FIELD), order.getStr(SORT)));
    		}
    		sqlOrder.setLength(sqlOrder.length() - 1); // 去掉末尾的逗号
    	}
    	if (!sqlOrder.isEmpty()) {
        	sql.append(String.format(" order by %s", sqlOrder.toString()));
    	}
    	
    	// 翻译limit
    	if (sqlParser.containsKey(LIMIT)) {
    		JSONObject limit = sqlParser.getJSONObject(LIMIT);
    		int pageNum = limit.getInt(PAGENUM);
    		int pageSize = limit.getInt(PAGESIZE);
    		int pageStart = (pageNum - 1) * pageSize;
    		sql.append(String.format(" limit %s,%s", pageStart, pageSize));
    	}
    	
    	// 保存参数
    	sqlParser.paramList = params;

    	return sql.toString();
    }
    
    private String getWhere(JSONObject whereObj) {
    	String field = whereObj.getStr(FIELD);
    	if (DbConfig.exists(table, field)) {
    		return String.format("`%s`", field);
    	} else {
    		return field;
    	}
    }
    
    /**
     * 获取sql查询的select字段，排除要过滤的
     * @param sqlParser
     * @return
     */
    private String getFilterSelectFields(SqlParser sqlParser) {
    	String selectFields = sqlParser.getStr(SELECT);
    	String filterFields = sqlParser.getStr(SELECT_FILTER);
    	
    	if (StrUtil.isNotBlank(filterFields)) {
    		String[] filterFieldsArr = filterFields.split(",");
    		List<Column> columns = DbConfig.columnList(sqlParser.getTable());
    		return columns.stream().filter(c -> {
    			return Arrays.stream(filterFieldsArr).noneMatch(f -> f.equals(c.getCode()));
    		}).map(c -> getColumnCode(c))
    		.collect(Collectors.joining(","));
    	} else if (selectFields != null) {
    		return selectFields;
    	} else {
    		List<Column> columns = DbConfig.columnList(sqlParser.getTable());
    		return columns.stream().map(c -> getColumnCode(c))
    		.collect(Collectors.joining(","));
    	}
	}
    
    private String getColumnCode(Column column) {
    	if ("BITMAP_UNION".equalsIgnoreCase(column.getFun())) {
    		return String.format("bitmap_count(`%s`) as %s", column.getCode(), column.getCode());
    	}
    	return String.format("`%s`", column.getCode());
    }

	/**
     * 测试对象是否可转为SqlParser对象
     * @return
     */
    private SqlParser getSubSqlParser(JSONObject where) {
    	try {
    		if (where != null) {
    			SqlParser sql = where.getBean(VALUE, SqlParser.class);
    			if (sql != null && sql.containsKey("table")) {
    				sql.setTable(where.getJSONObject(VALUE).getStr("table"));
    				return sql;
    			}
    		}
    		return null;
    	} catch (Exception e) {
    		return null;
    	}
    }

    /**
     * 翻译sqlJSON字段为原生sql
     * @param 
     * @return
     */
    @SuppressWarnings("unchecked")
	private String getOriginalSql(SqlParser sqlParser) {
    	// select * from `user` where `a` = ? and `b` = ? group by age,name having count(*)>1 order by age desc limit 1,10
    	StringBuilder sql = new StringBuilder();
    	sql.append("select ");
    	
    	// 翻译查询的select
    	String filterSelectFields = getFilterSelectFields(sqlParser);
		sql.append(filterSelectFields).append(String.format(" from `%s`", sqlParser.getTable()));
    	
    	// 翻译查询的where
    	StringBuilder sqlWhere = new StringBuilder();
    	JSONArray arrWhere = sqlParser.getJSONArray(WHERE);
    	if (arrWhere != null && !arrWhere.isEmpty()) {
    		for (int i = 0; i < arrWhere.size(); i++) {
    			JSONObject where = arrWhere.getJSONObject(i);
    			Object value = where.get(VALUE);
    			String operator = where.getStr(OPERATOR).toLowerCase();
    			SqlParser subSqlCreator = getSubSqlParser(where);
				if (subSqlCreator != null) {
					String subQuery = getSql(subSqlCreator);
    				sqlWhere.append(String.format("%s %s (%s) and ", getWhere(where), operator, subQuery));
    			} else {
    				if ("in".equals(operator) || "not in".equals(operator)) {
    					List<Object> valueArr = (List<Object>)value;
    					sqlWhere.append(String.format("%s %s (", getWhere(where), where.getStr(OPERATOR)));
    					valueArr.stream().forEach(v -> {
	    					sqlWhere.append(v.toString() + ",");
    					});
    					sqlWhere.deleteCharAt(sqlWhere.length() - 1).append(String.format(") and "));
    				} else {
		    			sqlWhere.append(String.format("%s %s %s and ", getWhere(where), operator, value));
    				}
    			}
    		}
    		sqlWhere.setLength(sqlWhere.length() - 5); // 去掉末尾的and
    	}
    	if (!sqlWhere.isEmpty()) {
        	sql.append(String.format(" where sys_deleted=1 and %s", sqlWhere.toString()));
    	} else {
    		sql.append(" where sys_deleted=1");
    	}
    	
    	// 翻译查询的group
    	String groupFields = sqlParser.getStr(GROUP);
		sql.append(groupFields != null ? String.format(" group by %s", groupFields) : "");
    	
    	// 翻译查询的having
    	StringBuilder sqlHaving = new StringBuilder();
    	JSONArray arrHaving = sqlParser.getJSONArray(HAVING);
    	if (arrHaving != null && !arrHaving.isEmpty()) {
    		for (int i = 0; i < arrHaving.size(); i++) {
    			JSONObject having = arrHaving.getJSONObject(i);
    			sqlHaving.append(String.format("%s %s %s and ", having.getStr(FIELD), having.getStr(OPERATOR), 
    					having.get(VALUE)));
    		}
    		sqlHaving.setLength(sqlHaving.length() - 4); // 去掉末尾的and
    	}
    	if (!sqlHaving.isEmpty()) {
        	sql.append(String.format(" having %s", sqlHaving.toString()));
    	}
    	
    	// 翻译查询的order
    	StringBuilder sqlOrder = new StringBuilder();
    	JSONArray arrOrder = sqlParser.getJSONArray(ORDER);
    	if (arrOrder != null && !arrOrder.isEmpty()) {
    		for (int i = 0; i < arrOrder.size(); i++) {
    			JSONObject order = arrOrder.getJSONObject(i);
    			sqlOrder.append(String.format("`%s` %s,", order.getStr(FIELD), order.getStr(SORT)));
    		}
    		sqlOrder.setLength(sqlOrder.length() - 1); // 去掉末尾的逗号
    	}
    	if (!sqlOrder.isEmpty()) {
        	sql.append(String.format(" order by %s", sqlOrder.toString()));
    	}
    	
    	// 翻译limit
    	if (sqlParser.containsKey(LIMIT)) {
    		JSONObject limit = sqlParser.getJSONObject(LIMIT);
    		int pageNum = limit.getInt(PAGENUM);
    		int pageSize = limit.getInt(PAGESIZE);
    		int pageStart = (pageNum - 1) * pageSize;
    		sql.append(String.format(" limit %s,%s", pageStart, pageSize));
    	}

    	return sql.toString();
    }
}
